﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects.DataClasses;
using DBService.ViewDatas;
using System.Collections;
using System.Data.Objects;
//
//  CONNECTION STRING
//  
//
namespace DBService
{
    public class ModelAdapter
    {
        #region --Constants---

        //UsreType 1=admin,0=simpleUser


        #endregion

        #region ---variables---

        private revioEntities objectContext = new revioEntities();

        #endregion

        #region ---Files---

        public int FileAction(int? fileID, string fileName, string title)
        {
            int insertedId = 1;


            if (fileID.HasValue) //update
            {
                var update_Item = objectContext.Files.Where(f => f.FileID == fileID).FirstOrDefault();
                update_Item.FilePath = fileName;
                objectContext.SaveChanges();
                return insertedId;
            }
            else //insert
            {
                objectContext.Files.AddObject(new File() { FilePath = fileName, CreateDate = DateTime.Now, Title = title });
                objectContext.SaveChanges();
                return insertedId = 0;
            }
        }
        public int DeleteFileByID(int FileID)
        {
            // TODO Delete file by file idmco

            var del_Item = objectContext.Files.Where(f => f.FileID == FileID).FirstOrDefault();
            if (del_Item != null)
            {
                objectContext.Files.DeleteObject(del_Item);
                objectContext.SaveChanges();
                return 1;
            }
            else
                return 0;
        }
        public List<File> GetFiles(int? fileID, int? galleryID)
        {
            // TODO return file by fileID if fileID is not null
            // apply other filters too
            if (fileID.HasValue)
            {
                return objectContext.Files.Where(f => f.FileID == fileID).ToList();

            }
            else if (galleryID.HasValue)
            {
                var items = from f in objectContext.Files.Include("Galleries")
                            from g in f.GalleryFiles
                            where g.GalleryID == galleryID
                            select f;
                return items.ToList();

            }
            return null;
        }

        #endregion

        #region ---Gallery---


        public int GalleryAction(int? galleryID, string title, File file, Company company, Review review)
        {
            int insertedId = 1;

            if (galleryID.HasValue) //update
            {
                var update_Item = objectContext.Galleries.Where(g => g.GalleryID == galleryID).FirstOrDefault();
                update_Item.Title = title;
                objectContext.SaveChanges();
                return insertedId = 0;

            }
            else //insert
            {
                Gallery gallery = new Gallery();

                //add company's gallery
                if (company != null)
                {
                    // gallery.GalleryFiles.Add(file);
                    gallery.CreateDate = DateTime.Now;
                    gallery.Companies.Add(company);
                    objectContext.Galleries.AddObject(gallery);
                }
                //add review's gallery
                else if (review != null)
                {
                    //gallery.Files.Add(file);
                    gallery.CreateDate = DateTime.Now;
                    gallery.Reviews.Add(review);
                    objectContext.Galleries.AddObject(gallery);
                }

                objectContext.SaveChanges();
                return insertedId;
            }
        }
        public int DeleteGalleryByID(int galleryID)
        {
            var del_Item = objectContext.Galleries.Where(f => f.GalleryID == galleryID).FirstOrDefault();
            if (del_Item != null)
            {
                objectContext.Galleries.DeleteObject(del_Item);
                objectContext.SaveChanges();
                return 1;
            }
            else
                return 0;
        }
        public List<Gallery> GetGalleries(int? galleryID, int? compayID, int? reviewID)
        {
            if (galleryID.HasValue)
            {
                return objectContext.Galleries.Include("Files").Include("Companies").Include("Reviews").Where(g => g.GalleryID == galleryID).ToList();

            }
            else if (compayID.HasValue)
            {
                var items = from g in objectContext.Galleries.Include("Companies")
                            from c in g.Companies
                            where c.GalleryID == galleryID
                            select g;
                return items.ToList();

            }
            else if (reviewID.HasValue)
            {
                var items = from g in objectContext.Galleries.Include("Reviews")
                            from r in g.Reviews
                            where r.GalleryID == galleryID
                            select g;
                return items.ToList();
            }
            return null;
        }


        #endregion

        #region ---Review---

        public void changeReviewCompanyByID(int ID, int CompanyID)
        {
            var rev = objectContext.Reviews.Where(r => r.ReviewID == ID && r.IsDeleted == null).FirstOrDefault();
            if (rev != null)
            {
                rev.CompanyID = CompanyID;
                objectContext.SaveChanges();
            }
        }

        public void RestoreRreviwByID(int ReviewID)
        {
            var rev = objectContext.Reviews.Where(r => r.ReviewID == ReviewID).FirstOrDefault();
            if (rev != null)
            {
                rev.IsDeleted = null;
                objectContext.SaveChanges();
            }
        }

        public void DeleteReviewByID(int ID)
        {
            var rev = objectContext.Reviews.Where(r => r.ReviewID == ID).FirstOrDefault();
            if (rev != null)
            {
                rev.IsDeleted = true;
                if (rev.Company != null)
                {
                    CalculateCompanyRating(rev.CompanyID);
                }
                objectContext.SaveChanges();
            }
        }

        public List<Review> getReviewByCompanyIDOrAll(int? companyID)
        {
            if (companyID != null)
            {
                return objectContext.Reviews.Include("Company").Include("User").Where(r => r.CompanyID == companyID && r.IsDeleted == true).ToList();
            }
            else
                return objectContext.Reviews.Where(r => r.IsDeleted == true).ToList();

        }

        public List<Review> GetReviewForMergeCompanies(int companyID)
        {
            return objectContext.Reviews.Include("Company").Include("User").Include("Gallery").Where(r => r.CompanyID == companyID && r.IsDeleted == null).ToList();
        }

        public List<ReviewInfo> GetLatestReviewForSearch(int? TopSize)
        {
            var newReviewItems = from review in objectContext.Reviews
                                 join company in objectContext.Companies on review.CompanyID equals company.CompanyID
                                 join user in objectContext.Users on review.UserID equals user.UserID
                                 orderby review.ReviewID descending
                                 where review.IsDeleted == null
                                 select new ReviewInfo
                                 {
                                     userFullName = user.FirstName + " " + user.LastName,
                                     review = review.Review1,
                                     avatar = user.Avatar,
                                     userId = user.UserID,
                                     companyName = company.Name,
                                     CompanyID = company.CompanyID,
                                     createDate = review.CreateDate == null ? DateTime.Now : review.CreateDate.Value,
                                     reviewID = review.ReviewID,
                                     UserFB = user.FaceID.HasValue ? user.FaceID : null
                                 };

            if (TopSize.HasValue)
            {
                return newReviewItems.Take(TopSize.Value).ToList();
            }
            else
                return newReviewItems.ToList();

        }

        public List<ReviewInfo> GetLatestReviewForHomePage(int? TopSize, int? userID)
        {
            List<ReviewInfo> _result = new List<ReviewInfo>();
            var subScribeuserItems = new List<ReviewInfo>().AsQueryable();

            var newReviewItems = from review in objectContext.Reviews
                                 join company in objectContext.Companies on review.CompanyID equals company.CompanyID
                                 join user in objectContext.Users on review.UserID equals user.UserID
                                 orderby review.ReviewID descending
                                 where review.IsDeleted == null
                                 select new ReviewInfo
                                 {
                                     userFullName = user.FirstName + " " + user.LastName,
                                     review = review.Review1,
                                     avatar = user.Avatar,
                                     userId = user.UserID,
                                     companyName = company.Name,
                                     CompanyID = company.CompanyID,
                                     createDate = review.CreateDate == null ? DateTime.Now : review.CreateDate.Value,
                                     reviewID = review.ReviewID,
                                     UserFB = user.FaceID != null ? user.FaceID : null
                                 };
            if (userID != null)
            {
                subScribeuserItems = from review in objectContext.Reviews
                                     join company in objectContext.Companies on review.CompanyID equals company.CompanyID
                                     join user in objectContext.Users on review.UserID equals user.UserID
                                     join suser in objectContext.SubScriberUsers on user.UserID equals suser.SubScriberUserID
                                     orderby review.ReviewID descending
                                     where suser.UserID == userID && suser.IsDeleted == null && review.IsDeleted == null
                                     select new ReviewInfo
                                     {
                                         userFullName = user.FirstName + " " + user.LastName,
                                         review = review.Review1,
                                         avatar = user.Avatar,
                                         userId = user.UserID,
                                         companyName = company.Name,
                                         CompanyID = company.CompanyID,
                                         createDate = review.CreateDate == null ? DateTime.Now : review.CreateDate.Value,
                                         reviewID = review.ReviewID,
                                         UserFB = user.FaceID != null ? user.FaceID : null
                                     };
            }

            if (subScribeuserItems.Count() > 0)
            {
                _result.AddRange(subScribeuserItems.ToList());
                var dif = TopSize - subScribeuserItems.Count();
                if (dif > 0)
                {
                    _result.AddRange(newReviewItems.Take(dif.Value));
                    return _result.DistinctBy(r => r.reviewID).ToList();
                }
                else
                    return _result.DistinctBy(r => r.reviewID).ToList();
            }

            if (TopSize.HasValue)
            {
                return newReviewItems.Take(TopSize.Value).ToList();
            }
            else
                return newReviewItems.ToList();

        }

        public List<FirstTimeReviews_Result> GetFirstTimeReviews()
        {
            var items = objectContext.FirstTimeReviews();
            return items.ToList();
        }

        public List<TrandingReviews_Result> GetTrandingTimeReviews()
        {
            var items = objectContext.TrandingReviews();
            return items.ToList();
        }

        public int ReviewAction(int? reviewID, string reviewText, double? rateing, int CompanyID, int UserID, List<string> images)
        {
            if (reviewID.HasValue)
            {

            }
            else
            {
                Gallery gallery = gallery = new Gallery();
                Review _review = new Review();
                _review.Review1 = reviewText;
                _review.Rating = rateing;
                _review.CompanyID = CompanyID;
                _review.UserID = UserID;
                _review.Gallery = gallery;
                _review.CreateDate = DateTime.Now;
                objectContext.Reviews.AddObject(_review);
                objectContext.SaveChanges();
                Company company = objectContext.Companies.Where(c => c.CompanyID == CompanyID).FirstOrDefault();
                if (rateing != 0)
                {

                    CalculateCompanyRating(company.CompanyID);//company.Rating.HasValue ? ((company.Rating + rateing.Value) / 2) : rateing.Value;
                }
                if (company.GalleryID == null)
                {
                    company.Gallery = new Gallery();
                    company.Gallery.CreateDate = DateTime.Now;
                }
                if (images.Count() != 0)
                {
                    foreach (var item in images)
                    {
                        File file = new File();
                        file.FilePath = item;
                        file.CreateDate = DateTime.Now;
                        objectContext.Files.AddObject(file);



                        var galleryFile = new GalleryFile();
                        galleryFile.GalleryID = company.GalleryID.Value;
                        galleryFile.File = file;
                        company.Gallery.GalleryFiles.Add(galleryFile);


                        var galleryfile1 = new GalleryFile();
                        galleryFile.File = file;
                        galleryfile1.Gallery = gallery;
                        objectContext.GalleryFiles.AddObject(galleryfile1);
                        objectContext.SaveChanges();
                    }
                }

                objectContext.SaveChanges();
                return 1;

            }
            return 0;
        }

        //public int ReviewAction(int? reviewID, string reviewText, double? rating, int? Likes, int? DisLikes, byte? flags, int? galleryID, Company company, Gallery gallery)
        //{
        //    int insertedId = 1;

        //    if (reviewID.HasValue)
        //    {
        //        var update_Item = objectContext.Reviews.Where(g => g.ReviewID == reviewID).FirstOrDefault();
        //        //  update_Item.ReviewText = reviewText == null ? update_Item.ReviewText : reviewText;
        //        update_Item.Rating = rating == null ? update_Item.Rating : rating;
        //        update_Item.Likes = Likes == null ? update_Item.Likes : Likes;
        //        update_Item.DisLikes = DisLikes == null ? update_Item.DisLikes : DisLikes;
        //        update_Item.Flags = flags == null ? update_Item.Flags : flags;
        //        update_Item.GalleryID = galleryID == null ? update_Item.GalleryID : galleryID;
        //        update_Item.ModifyDate = DateTime.Now;
        //        objectContext.SaveChanges();
        //        return insertedId = 0;

        //    }
        //    else
        //    {
        //        Review review = new Review();
        //        review.Company = company;
        //        // review.ReviewText = reviewText;
        //        review.Rating = rating == null ? 0 : rating.Value;
        //        review.Likes = Likes == null ? 0 : Likes.Value;
        //        review.DisLikes = DisLikes == null ? 0 : DisLikes;
        //        review.Flags = flags == null ? 0 : flags;
        //        review.ModifyDate = DateTime.Now;
        //        objectContext.SaveChanges();
        //        return insertedId;
        //    }
        //}

        public void CalculateCompanyRating(int CompanyID)
        {
            var company = objectContext.Companies.Where(c => c.CompanyID == CompanyID && c.IsDeleted == null).FirstOrDefault();
            var reviews = objectContext.Reviews.Where(r => r.IsDeleted == null && r.CompanyID == CompanyID);
            company.Rating = reviews.Count() > 0 ? (double)reviews.Sum(r => r.Rating) / reviews.Count() : 0;
            objectContext.SaveChanges();
        }

        public List<Review> GetReviewsByCompanyID(int CompanyID)
        {
            return objectContext.Reviews.Include("User").Include("Gallery").Where(r => r.CompanyID == CompanyID && r.IsDeleted == null).ToList();
        }

        public bool ReviewDelete(int ReviewID)
        {
            try
            {
                var reviewToDelete = objectContext.Reviews.Where(r => r.ReviewID == ReviewID).SingleOrDefault();
                if (reviewToDelete != null)
                {
                    //foreach (var item in reviewToDelete.Gallery.GalleryFiles)
                    //{
                    //    try
                    //    {
                    //        DBService.Admin.CompanyManager.DeleteImage(item.File.FilePath);
                    //    }
                    //    catch (Exception ex)
                    //    {
                    //        // TODO log delete image exc.
                    //    }
                    //}
                    //objectContext.Reviews.DeleteObject(reviewToDelete);
                    reviewToDelete.IsDeleted = true;
                    objectContext.SaveChanges();
                    CalculateCompanyRating(reviewToDelete.CompanyID);
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public bool ReviewLike(int ReviewID, int UserID)
        {
            try
            {
                var Rev = objectContext.Reviews.Where(r => r.ReviewID == ReviewID && r.IsDeleted == null).SingleOrDefault();
                if (Rev == null)
                    return false;
                Rev.Likes = (Rev.Likes ?? 0) + 1;
                objectContext.SaveChanges();
                AddUserReviews(Rev.ReviewID, UserID, true, null, 1);
                return true;
            }
            catch
            {
                return false;
            }
        }

        public bool ReviewDisLike(int ReviewID, int UserID)
        {
            try
            {
                var Rev = objectContext.Reviews.Where(r => r.ReviewID == ReviewID && r.IsDeleted == null).SingleOrDefault();
                if (Rev == null)
                    return false;
                Rev.DisLikes = (Rev.DisLikes ?? 0) + 1;
                objectContext.SaveChanges();
                AddUserReviews(Rev.ReviewID, UserID, null, true, 1);
                return true;
            }
            catch
            {
                return false;
            }
        }

        public Review GetLastReviewByUserAndCompanyID(int UserID, int CompanyID)
        {
            var reviews = objectContext.Reviews.Where(r => r.UserID == UserID && r.CompanyID == CompanyID && r.IsDeleted == null);
            return reviews != null ? reviews.OrderByDescending(r => r.CreateDate).FirstOrDefault() : null;
        }
        #endregion

        #region ---User--

        public User GetUserByMail(string mail)
        {
            var item = objectContext.Users.Where(u => u.Email == mail);
            return item == null ? null : item.FirstOrDefault();
        }

        public int UserAction(int? userID, string avatar, string firstName, string lastName, string eMail, string password,
            bool? gender, int day, int month, int year, List<int> categoryIDs, int userType)
        {
            int InserteduserID = 0;
            if (userID.HasValue)
            {

            }
            else if (userID == null)
            {

                User user = new User();

                user.FirstName = firstName;
                user.LastName = lastName;
                user.Email = eMail;
                user.Password = password;
                user.Gender = gender;
                user.CreateDate = DateTime.Now;
                user.DateOfBirth = new DateTime(year, month, day);
                user.UserType = (short)userType;
                user.Avatar = avatar;
                objectContext.Users.AddObject(user);

                foreach (int item in categoryIDs)
                {
                    UserCategory usercategory = new UserCategory();
                    Category category = objectContext.Categories.Where(c => c.CategoryID == item).FirstOrDefault();
                    if (category != null)
                    {
                        usercategory.Category = category;
                        user.UserCategories.Add(usercategory);
                    }
                }
                objectContext.SaveChanges();
                InserteduserID = user.UserID;
            }
            return InserteduserID;
        }


        public int EdiUserProfile(int userID
                                  , string avatar
                                  , string firstName
                                  , string lastName
                                  , string eMail
                                  , string oldpassword
                                  , string newPassword
                                  , int userType)
        {
            var user = objectContext.Users.Where(u => u.UserID == userID).FirstOrDefault();
            user.Avatar = avatar == null ? user.Avatar : avatar;
            user.LastName = lastName == null ? user.LastName : lastName;
            user.FirstName = firstName == null ? user.FirstName : firstName;
            if (!string.IsNullOrEmpty(newPassword))
            {
                user.Password = newPassword;
            }
            user.UserType = 1;
            objectContext.SaveChanges();
            return 0;
        }


        public List<UserProfileReviewInfo> GetUserProfileReviewInfos(int userID)
        {
            List<UserProfileReviewInfo> userProfile_list = new List<UserProfileReviewInfo>();
            //List<Review> reviews = objectContext.Reviews.Where(r => r.UserID == userID).ToList();

            var user = objectContext.Users.Where(u => u.UserID == userID).FirstOrDefault();
            var userProfilewithoutReviews = new UserProfileReviewInfo();
            userProfilewithoutReviews.FirstName = user.FirstName;
            userProfilewithoutReviews.LastName = user.LastName;
            userProfilewithoutReviews.Avatar = user.Avatar;

            //var compa = objectContext.Companies.Where(c => c.UserID == userID).FirstOrDefault();
            //string CompanyName = compa.Name;
            //int CompID = compa.CompanyID;
            //int ReviewCount = reviews.Count();
            // if (reviews.Count != 0)
            //{
            var rev_list = from rev in objectContext.Reviews
                           join company in objectContext.Companies on rev.CompanyID equals company.CompanyID
                           where rev.UserID == userID && rev.IsDeleted == null
                           select new UserProfileReviewInfo
                           {
                               FirstName = userProfilewithoutReviews.FirstName,
                               LastName = userProfilewithoutReviews.LastName,
                               Avatar = userProfilewithoutReviews.Avatar,
                               Reviews = rev.Review1,
                               CreateDate = rev.CreateDate,
                               CompanyName = company.Name,
                               CompanyID = company.CompanyID,
                               ReviewGalleryObj = company.Gallery,
                               ReviewID = rev.ReviewID
                           };
            if (rev_list.Count() > 0)
            {
                userProfile_list.AddRange(rev_list.ToList());
            }
            //foreach (var item in reviews)
            //{
            //    var compa = objectContext.Companies.Where(c => c.CompanyID == item.CompanyID).FirstOrDefault();
            //    //string CompanyName = compa.Name;
            //    var userProfile = new UserProfileReviewInfo();
            //    userProfile.FirstName = userProfilewithoutReviews.FirstName;
            //    userProfile.LastName = userProfilewithoutReviews.LastName;
            //    userProfile.Avatar = userProfilewithoutReviews.Avatar;
            //    userProfile.Reviews = item.Review1;
            //    userProfile.CreateDate = item.CreateDate;
            //    userProfile.CompanyName = compa.Name;
            //    userProfile.CompanyID = item.CompanyID;
            //    userProfile.ReviewGalleryObj = item.Gallery;
            //    userProfile.ReviewID = item.ReviewID;
            //    userProfile_list.Add(userProfile);
            //}
            // }
            else
            {
                userProfile_list.Add(userProfilewithoutReviews);
            }
            return userProfile_list;
        }


        public UserInfo GetUser(int? UserID)
        {
            if (UserID.HasValue)
            {
                List<Review> reviews = new List<Review>();
                User user = objectContext.Users.Where(u => u.UserID == UserID).FirstOrDefault();
                UserInfo userInfo = new UserInfo();
                var userRating = objectContext.GetUserRating2(user.UserID).AsQueryable().ToList();
                userInfo.FirstName = user.FirstName;
                userInfo.LastName = user.LastName;
                userInfo.bithDate = (DateTime)user.DateOfBirth;
                userInfo.gender = user.Gender == true;
                userInfo.mail = user.Email;
                reviews = user.Reviews.Where(r => r.IsDeleted == null).ToList();
                userInfo.LastReviewDate = (DateTime?)reviews.Max(r => r.CreateDate);
                userInfo.Rateing = userRating.FirstOrDefault() != null ? double.Parse(userRating.FirstOrDefault().UserRating.ToString()) : 0; //reviews == null || reviews.Count == 0 ? 0 : (double)reviews.Sum(r => r.Rating) / reviews.Count();
                userInfo.ReviewCount = user.Reviews != null ? user.Reviews.Where(r => r.IsDeleted != true).ToList().Count : 0;
                userInfo.Avatar = user.Avatar;
                userInfo.UserID = user.UserID;
                userInfo.UserFB = user.FaceID != null ? user.FaceID : null;
                userInfo.Signature = user.Signature;
                return userInfo;
            }
            return null;
        }

        public bool AuthenticateUser(string mail, string password, out User userObj)
        {
            userObj = objectContext.Users.Where(u => u.Email == mail && u.Password == password).FirstOrDefault();
            if (userObj != null)
            {
                return true;
            }
            return false;
        }

        public List<TopMembers> GetTopMembers()
        {
            var list = (from member in objectContext.GetUserRating()
                        select new TopMembers
                        {
                            FirstName = member.firstname,
                            LastName = member.lastname,
                            avatar = member.avatar,
                            UserId = member.userid,
                            SumLike = member.like_sum,
                            CreateDate = member.createdate,
                            UserRating = member.user_rating,
                            UserFB = member.facebook != null ? member.facebook : null
                        });
            var items = list.Take(8).ToList();

            return items;
        }

        public List<TopMembers> GetTopMembersForRatings()
        {

            var list = (from member in objectContext.GetUserRating()
                        select new TopMembers
                        {
                            FirstName = member.firstname,
                            LastName = member.lastname,
                            avatar = member.avatar,
                            UserId = member.userid,
                            SumLike = member.like_sum,
                            CreateDate = member.createdate,
                            UserRating = member.user_rating,
                            UserFB = member.facebook != null ? member.facebook : null
                        });
            var items = list.ToList();

            return items;
        }

        public List<TopMembers> GetTopMembersForRatings2()
        {
            var list = (from member in objectContext.GetUserRating2(null)
                        select new TopMembers
                        {
                            FirstName = member.FirstName,
                            LastName = member.LastName,
                            avatar = member.Avatar,
                            UserId = member.UserID,
                            SumLike = member.LikesCount,
                            CreateDate = member.CreateDate,
                            UserRating = member.UserRating,
                            UserFB = member.facebook != null ? member.facebook : null
                        });
            var items = list.ToList();

            return items;
        }

        public int ImportUserFromFB(string FBID, string FirstName, string LastName, string Mail, bool Sex,
            DateTime BirthDate, short UserType, out User userObj)
        {
            int actionCode = -1;
            try
            {
                long usrFbID = long.Parse(FBID);
                var user = objectContext.Users.Where(u => u.FaceID == usrFbID).FirstOrDefault();
                if (user == null)
                {
                    user = new User()
                    {
                        FaceID = usrFbID,
                        FirstName = FirstName,
                        LastName = LastName,
                        Gender = Sex,
                        Password = Guid.NewGuid().ToString().Substring(10),
                        CreateDate = DateTime.Now,
                        Email = Mail,
                        UserType = UserType,
                        DateOfBirth = BirthDate
                    };
                    objectContext.Users.AddObject(user);
                    objectContext.SaveChanges();
                    actionCode = 1;
                }
                else
                {
                    actionCode = 2;
                }
                userObj = user;
                return actionCode;
            }
            catch (Exception ex)
            {
                actionCode = -1;
                userObj = null;
                return actionCode;
            }
        }

        public string GetUserMostActiveCategory(int userID)
        {
            var ct = objectContext.GetUserMostActiveCategories(userID);
            string result = string.Empty;
            var f = ct.OrderByDescending(c => c.Count).Take(1).FirstOrDefault();
            if (f != null)
            {
                var category = objectContext.Categories.Where(cat => cat.CategoryID == f.CategoryID).FirstOrDefault();
                if (category != null)
                {
                    result = category.Title;
                }
            }
            return result;
        }

        #endregion

        #region ---Category---

        public List<Category> GetCategoreis(int? CategoryID)
        {
            if (CategoryID.HasValue)
            {
                return objectContext.Categories.Where(c => c.CategoryID == CategoryID).ToList();
            }
            else
            {
                return objectContext.Categories.ToList();
            }
        }



        #endregion

        #region ---Company---

        public List<string> GetCompaniesNamesByName(string companyName)
        {
            return objectContext.Companies.Where(c => c.IsDeleted == null && c.Name.Contains(companyName)).Select(c => c.Name).ToList();
        }

        public void DeleteCompanyAndAllReviewByID(int ID)
        {
            var com = objectContext.Companies.Where(c => c.CompanyID == ID).FirstOrDefault();
            if (com != null)
            {
                var reviews = objectContext.Reviews.Where(r => r.CompanyID == ID);
                foreach (var review in reviews)
                {
                    review.IsDeleted = true;
                }
                com.IsDeleted = true;
                objectContext.SaveChanges();
            }
        }

        public void UpdateCompanyGallery(int Companyid, List<string> Gallerries)
        {
            var Company = objectContext.Companies.Where(c => c.CompanyID == Companyid);
            if (Company != null && Gallerries != null)
            {
                if (Company.FirstOrDefault().GalleryID != null)
                {
                    foreach (var item in Gallerries)
                    {
                        File file = new File();
                        file.CreateDate = DateTime.Now;
                        file.FilePath = item;
                        objectContext.Files.AddObject(file);
                        GalleryFile gf = new GalleryFile();
                        gf.File = file;
                        gf.Gallery = Company.FirstOrDefault().Gallery;
                        objectContext.GalleryFiles.AddObject(gf);
                    }
                    objectContext.SaveChanges();
                }
                else
                {
                    Gallery gallery = new Gallery();
                    gallery.CreateDate = DateTime.Now;
                    objectContext.Galleries.AddObject(gallery);
                    foreach (var item in Gallerries)
                    {
                        File file = new File();
                        file.CreateDate = DateTime.Now;
                        file.FilePath = item;
                        objectContext.Files.AddObject(file);
                        GalleryFile gf = new GalleryFile();
                        gf.File = file;
                        gf.Gallery = gallery;
                        objectContext.GalleryFiles.AddObject(gf);
                    }
                    Company.FirstOrDefault().Gallery = gallery;
                    objectContext.SaveChanges();
                }
            }
        }

        public List<TopCompanies_Result> GetTopCompanies(int? CategoryID)
        {
            var items = objectContext.TopCompanies(CategoryID);
            if (items == null)
            {
                return new List<TopCompanies_Result>();
            }
            return items.ToList();
        }

        public List<CompanyRatingInfo> GetTopCompanies()
        {
            var lastWeek = DateTime.Now.AddDays(-7);
            var n = new EntityCollection<Review>();
            return objectContext.Companies
                .OrderByDescending(c => c.Reviews.Where(r => r.CreateDate > lastWeek && c.IsDeleted == null).Sum(r => r.Rating))
                .Select(co => new CompanyRatingInfo()
                {
                    CompanyID = co.CompanyID,
                    CompanyName = co.Name,
                    CreateDate = co.CreateDate,
                    Logo = co.Logo,
                    Rating = co.Rating ?? 0,
                    ReviewsCount = co.Reviews.Count(),
                    WebSite = co.Website
                })
                .ToList();
        }

        public List<int> GetTopCategoryIDs()
        {
            List<int> resultlist = new List<int>();
            var result = objectContext.GetTopCompanyCategoryID();
            foreach (var item in result)
            {
                resultlist.Add(int.Parse(item.CategoryID.ToString()));

            }
            int j = resultlist.Count();
            while (j < 2)
            {
                resultlist.Add(-5 + j);
                j++;
            }

            //var t = from company in objectContext.Companies
            //        join companycategory in objectContext.CompanyCategories on company.CompanyID equals companycategory.CompanyID
            //        select new
            //        {
            //            com = company.Avarage
            //        };
            //IList<int> list = objectContext.Companies.Include("CompanyCategories").OrderByDescending(c => c.Rating.Value).Select(c => c.CompanyID).Distinct().ToList();

            //if (list != null)
            //{
            //    for (int i = 0; i < list.Count; i++)
            //    {
            //        int ID = list[i];
            //        var firstCategory = objectContext.CompanyCategories.Where(c => c.CompanyID == ID).FirstOrDefault().CategoryID;
            //        resultlist.Add(firstCategory);
            //        if (i >= 1)
            //            break;
            //    }
            //}            
            return resultlist;
        }

        public List<Company> GetNewCompaneis()
        {
            return objectContext.Companies.Where(c => c.IsDeleted == null).OrderByDescending(o => o.CreateDate).Take(8).ToList();
        }

        public int CompanyActionShort(int? companyID, string logo, string companyName,
            string address, string phone, string webSite, List<int> CategoryIDs,
            List<string> Galleries, int? UserID)
        {
            if (companyID.HasValue)
            {
                var compToUpadate = objectContext.Companies.Where(c => c.CompanyID == companyID).SingleOrDefault();
                if (compToUpadate == null)
                    return -1;

                foreach (int item in CategoryIDs)
                {
                    var ct = compToUpadate.CompanyCategories.Where(c => c.CategoryID == item).SingleOrDefault();
                    if (ct == null)
                    {
                        ct = new CompanyCategory();
                        ct.CategoryID = item;
                        ct.CompanyID = companyID.Value;
                        compToUpadate.CompanyCategories.Add(ct);
                    }
                }

                if (Galleries.Count() != 0)
                {
                    Gallery gallery;
                    if (compToUpadate.Gallery == null)
                    {
                        gallery = new Gallery();
                        gallery.CreateDate = DateTime.Now;
                        compToUpadate.Gallery = gallery;
                    }
                    else
                    {
                        gallery = compToUpadate.Gallery;
                    }
                    foreach (string item in Galleries)
                    {
                        GalleryFile galleryFile = new GalleryFile();
                        File file = new File();
                        file.CreateDate = DateTime.Now;
                        file.FilePath = item;

                        galleryFile.File = file;
                        gallery.GalleryFiles.Add(galleryFile);
                    }
                }

                compToUpadate.Logo = logo;
                compToUpadate.Name = companyName.Replace("'","`");
                compToUpadate.Address = address;
                compToUpadate.Phone = phone;
                compToUpadate.Website = webSite;

                objectContext.SaveChanges();
                return compToUpadate.CompanyID;
            }
            else
            {
                #region New Company
                Gallery gallery = null;
                Company _company = new Company();


                _company.Logo = logo;
                _company.Name = companyName.Replace("'", "`");
                _company.Address = address;
                _company.Phone = phone;
                _company.Website = webSite;
                _company.CreateDate = DateTime.Now;
                _company.UserID = UserID;
                if (UserID.HasValue)
                {
                    _company.User = objectContext.Users.Where(u => u.UserID == UserID).FirstOrDefault();
                }
                foreach (int item in CategoryIDs)
                {
                    Category category = objectContext.Categories.Where(c => c.CategoryID == item).FirstOrDefault();
                    CompanyCategory companyCategory = new CompanyCategory();
                    if (category != null)
                    {
                        companyCategory.Category = category;
                        _company.CompanyCategories.Add(companyCategory);
                    }

                }
                if (Galleries.Count() != 0)
                {
                    gallery = new Gallery();
                    gallery.CreateDate = DateTime.Now;
                }
                foreach (string item in Galleries)
                {
                    GalleryFile galleryFile = new GalleryFile();
                    File file = new File();
                    file.CreateDate = DateTime.Now;
                    file.FilePath = item;

                    galleryFile.File = file;
                    gallery.GalleryFiles.Add(galleryFile);
                }
                _company.Gallery = gallery;

                objectContext.Companies.AddObject(_company);
                objectContext.SaveChanges();

                return _company.CompanyID;
                #endregion
            }
        }


        public int CompanyAction(int? companyID, string logo, string companyName, string address, string map, string phone, string webSite,
                      bool? isWifi, bool? isOpenAir, bool? monday, bool? thuesday, bool? wednesday, bool? thursday,
                      bool? friday, bool? saturday, bool? sunday, short? workHoureFrom, short? workHoureTo,
                      int? priceFrom, int? priceTo, bool? gel, bool? usd, bool? eur, bool? music, bool? liveMusic,
                      bool? smokeing, bool? noSmoking, List<int> CategoryIDs, List<string> Galleries, int? UserID)
        {
            if (companyID.HasValue)
            {
                var compToUpadate = objectContext.Companies.Where(c => c.CompanyID == companyID).SingleOrDefault();
                if (compToUpadate == null)
                    return -1;

                foreach (int item in CategoryIDs)
                {
                    var ct = compToUpadate.CompanyCategories.Where(c => c.CategoryID == item).SingleOrDefault();
                    if (ct == null)
                    {
                        ct = new CompanyCategory();
                        ct.CategoryID = item;
                        ct.CompanyID = companyID.Value;
                        compToUpadate.CompanyCategories.Add(ct);
                    }
                }

                if (Galleries.Count() != 0)
                {
                    Gallery gallery;
                    if (compToUpadate.Gallery == null)
                    {
                        gallery = new Gallery();
                        gallery.CreateDate = DateTime.Now;
                        compToUpadate.Gallery = gallery;
                    }
                    else
                    {
                        gallery = compToUpadate.Gallery;
                    }
                    foreach (string item in Galleries)
                    {
                        GalleryFile galleryFile = new GalleryFile();
                        File file = new File();
                        file.CreateDate = DateTime.Now;
                        file.FilePath = item;

                        galleryFile.File = file;
                        gallery.GalleryFiles.Add(galleryFile);
                    }
                }

                compToUpadate.Logo = logo;
                compToUpadate.Name = companyName.Replace("'", "`"); ;
                compToUpadate.Address = address;
                compToUpadate.Phone = phone;
                compToUpadate.Website = webSite;
                compToUpadate.WiFi = isWifi;
                compToUpadate.OpenAir = isOpenAir;
                compToUpadate.Monday = monday;
                compToUpadate.Tuesday = thuesday;
                compToUpadate.Wednesday = wednesday;
                compToUpadate.Thursday = thursday;
                compToUpadate.Friday = friday;
                compToUpadate.Saturday = saturday;
                compToUpadate.Sunday = sunday;
                compToUpadate.WorkStartHour = workHoureFrom;
                compToUpadate.WorkEndHour = workHoureTo;
                compToUpadate.PriceFrom = priceFrom;
                compToUpadate.PriceTo = priceTo;
                compToUpadate.GEL = gel;
                compToUpadate.USD = usd;
                compToUpadate.EUR = eur;
                compToUpadate.Music = music;
                compToUpadate.LiveMusic = liveMusic;
                compToUpadate.Smoking = smokeing;
                compToUpadate.NoSmiking = noSmoking;
                compToUpadate.Map = map;

                objectContext.SaveChanges();
                return compToUpadate.CompanyID;
            }
            else
            {
                #region New Company
                Gallery gallery = null;
                Company _company = new Company();
                _company.CreateDate = DateTime.Now;
                _company.Music = music;
                _company.LiveMusic = liveMusic;
                _company.Smoking = smokeing;
                _company.NoSmiking = noSmoking;
                _company.Logo = logo;
                _company.Name = companyName.Replace("'", "`"); ;
                _company.Address = address;
                _company.Phone = phone;
                _company.Website = webSite;
                _company.WiFi = isWifi;
                _company.OpenAir = isOpenAir;
                _company.Monday = monday;
                _company.Thursday = thuesday;
                _company.Wednesday = wednesday;
                _company.Thursday = thursday;
                _company.Friday = friday;
                _company.Saturday = saturday;
                _company.Sunday = sunday;
                _company.WorkStartHour = workHoureFrom;
                _company.WorkEndHour = workHoureTo;
                _company.PriceFrom = priceFrom;
                _company.PriceTo = priceTo;
                _company.GEL = gel;
                _company.USD = usd;
                _company.EUR = eur;
                _company.Map = map;
                _company.UserID = UserID;
                if (UserID.HasValue)
                {
                    _company.User = objectContext.Users.Where(u => u.UserID == UserID).FirstOrDefault();
                }
                foreach (int item in CategoryIDs)
                {
                    Category category = objectContext.Categories.Where(c => c.CategoryID == item).FirstOrDefault();
                    CompanyCategory companyCategory = new CompanyCategory();
                    if (category != null)
                    {
                        companyCategory.Category = category;
                        _company.CompanyCategories.Add(companyCategory);
                    }

                }
                if (Galleries.Count() != 0)
                {
                    gallery = new Gallery();
                    gallery.CreateDate = DateTime.Now;
                }
                foreach (string item in Galleries)
                {
                    GalleryFile galleryFile = new GalleryFile();
                    File file = new File();
                    file.CreateDate = DateTime.Now;
                    file.FilePath = item;

                    galleryFile.File = file;
                    gallery.GalleryFiles.Add(galleryFile);
                }
                _company.Gallery = gallery;

                objectContext.Companies.AddObject(_company);
                objectContext.SaveChanges();

                return _company.CompanyID;
                #endregion
            }
        }

        public bool AddCompanyOption(int companyID, int? modifierUseID, string key,
            string value, CompanyOptionType optionType, byte? sortIndex)
        {
            try
            {
                using (var context = new revioEntities())
                {
                    var singleCompany = context.Companies.Where(c => c.CompanyID == companyID).SingleOrDefault();
                    if (singleCompany == null)
                    {
                        return false;
                    }
                    if (string.IsNullOrEmpty(key) && string.IsNullOrEmpty(value))
                    {
                        return false;
                    }
                    var co = new CompanyOption()
                    {
                        CompanyID = companyID,
                        ModifierUserID = modifierUseID,
                        OptionName = key,
                        Value = value,
                        OptionTypeID = (int)optionType,
                        SortIndex = sortIndex
                    };
                    context.CompanyOptions.AddObject(co);
                    context.SaveChanges();
                }
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public bool AddCompanyOptions(List<CompanyOption> companyOptions, int companyID, int? userID,
            bool addAddressToCompany = false)
        {
            try
            {
                if (companyOptions == null || companyOptions.Count == 0)
                {
                    return false;
                }
                using (var context = new revioEntities())
                {
                    var singleCompany = context.Companies.Where(c => c.CompanyID == companyID).SingleOrDefault();
                    if (singleCompany == null)
                    {
                        return false;
                    }
                    if (addAddressToCompany)
                    {
                        var item = companyOptions.Where(c => c.OptionTypeID == (int)CompanyOptionType.ADDRESS_PHONE
                            && !string.IsNullOrEmpty(c.OptionName)).FirstOrDefault();
                        if (item != null)
                        {
                            singleCompany.Address = item.OptionName;
                            singleCompany.Phone = item.Value;
                        }
                    }
                    foreach (var co in companyOptions)
                    {
                        if (string.IsNullOrEmpty(co.OptionName) && string.IsNullOrEmpty(co.Value))
                        {
                            continue;
                        }
                        co.CompanyID = companyID;
                        co.ModifierUserID = userID;
                        context.CompanyOptions.AddObject(co);
                    }
                    context.SaveChanges();
                }
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public List<CompanyOption> GetCompanyOptions(int companyID)
        {
            try
            {
                using (var context = new revioEntities())
                {
                    var list = context.CompanyOptions.Where(co => co.CompanyID == companyID).ToList();
                    return list;
                }
            }
            catch (Exception ex)
            {
                return new List<CompanyOption>();
            }
        }

        public List<Company> GetCompanies(int? companyID)
        {
            if (companyID.HasValue)
            {
                return objectContext.Companies.Include("CompanyCategories").Include("Gallery").Where(c => c.CompanyID == companyID && c.IsDeleted == null).ToList();
            }
            else
            {
                return objectContext.Companies.Include("CompanyCategories").Include("Gallery").Where(c => c.IsDeleted == null).ToList();
            }
        }

        public Company GetCompanyByID(int companyID)
        {
            return objectContext.Companies.Where(c => c.CompanyID == companyID && c.IsDeleted == null).FirstOrDefault();
        }

        public List<Company> GetCompaniesForFilter()
        {
            return objectContext.Companies.Include("Reviews").Where(c => c.IsDeleted == null).ToList();
        }

        public bool CompanyCategoryDelete(int CategoryID, int CompanyID)
        {
            try
            {
                var comp = objectContext.Companies.Where(c => c.CompanyID == CompanyID).SingleOrDefault();
                if (comp == null)
                    return false;
                var compCatToDelete = comp.CompanyCategories.Where(cc => cc.CategoryID == CategoryID).SingleOrDefault();
                if (compCatToDelete == null)
                    return false;
                objectContext.CompanyCategories.DeleteObject(compCatToDelete);
                objectContext.SaveChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }


        /// <summary>
        /// აბრუნებს სათაურითა და ვებსაიტით მოძებნილ კომპანიებს
        /// </summary>
        /// <param name="searchText"></param>
        /// <returns></returns>
        public List<ListSearchCompanies_Result> GetSearchedCompanies(string searchText)
        {
            if (string.IsNullOrEmpty(searchText))
                return objectContext.ListSearchCompanies(null).ToList();
            return objectContext.ListSearchCompanies(searchText).ToList();
        }


        public List<Company> GetCompaniesForFilterPage()
        {
            return objectContext.Companies.Include("Reviews").Where(c => c.IsDeleted == null).ToList();
        }

        public List<CompanyRatingInfo> GetCompanyRatingsInfo()
        {

            var companyRatingInfo = from temp in objectContext.Companies
                                    orderby temp.Rating
                                    where temp.IsDeleted == null
                                    select new CompanyRatingInfo
                                    {
                                        CompanyName = temp.Name,
                                        ReviewsCount = temp.Reviews.Count(),
                                        Logo = temp.Logo,
                                        WebSite = temp.Website,
                                        Rating = (double)(temp.Rating == null ? 0 : temp.Rating),
                                        CompanyID = temp.CompanyID,
                                        CreateDate = temp.CreateDate
                                    };

            return companyRatingInfo.ToList();
        }


        #endregion

        #region ---procedure---

        public List<User_Profile_Info_Result> GetUserProfileInfos(int userID, int pageStartIndex, int pageSize, out int param)
        {
            System.Data.Objects.ObjectParameter output = new System.Data.Objects.ObjectParameter("RecordCount", typeof(int));
            var items = objectContext.User_Profile_Info(userID, pageStartIndex, pageSize, output);
            param = Convert.ToInt32(output);
            return items.ToList();
        }

        public List<List_RelatedCompanies_Result> GetRelatedCompanies(int CompID)
        {
            var retComps = objectContext.List_RelatedCompanies(CompID);
            return retComps.ToList();
        }

        #endregion

        #region ---Question---

        public int QuestionAction(int? QuestionID, string Answer, string Name, string Email, string QuestionText, bool? IsApproved)
        {
            if (QuestionID.HasValue)
            {

            }
            else
            {
                Question question = new Question();
                question.AuthorName = Name;
                question.AuthorEmail = Email;
                question.QuestionText = QuestionText;
                question.IsApproved = IsApproved;
                question.CreateDate = DateTime.Now;
                question.Answer = Answer;
                objectContext.Questions.AddObject(question);
                objectContext.SaveChanges();
                return 1;
            }
            return 0;
        }
        public List<Question> GetQuestionByCategoryID(int CategoryID)
        {
            return objectContext.Questions.Where(q => q.QuestionCategoryID == CategoryID).ToList();
        }

        public List<Question> GetQuestion(int? QuestionID)
        {
            if (QuestionID.HasValue)
            {
                return objectContext.Questions.Where(q => q.QestionID == QuestionID && !string.IsNullOrEmpty(q.Answer)).ToList();
            }

            else
            {
                return objectContext.Questions.Where(q => !string.IsNullOrEmpty(q.Answer)).ToList();
            }
        }

        public List<QuestionCategory> GetQuestionCategroy(int? questioCategoryID)
        {
            if (questioCategoryID.HasValue)
            {
                return objectContext.QuestionCategories.Where(q => q.QuestionCategoryID == questioCategoryID).ToList();
            }
            else
                return objectContext.QuestionCategories.ToList();
        }

        public List<Question> GetQuestionOnSerch(string question)
        {
            if (string.IsNullOrEmpty(question))
            {
                return objectContext.Questions.ToList();
            }
            else
            {
                return objectContext.Questions.Where(q => q.QuestionText.Contains(question)).ToList();
            }
        }


        #endregion

        #region ---ReviewUser---

        public void AddUserReviews(int revID, int UserID, bool? like, bool? disLike, int ActionID)
        {
            var urinfo = objectContext.UserReviews.FirstOrDefault(u => u.UserID == UserID && u.ReviewID == revID);
            if (urinfo != null)
            {
                urinfo.IsLIke = like;
                urinfo.IsDislike = disLike;
                objectContext.SaveChanges();
            }
            else
            {
                UserReview ur = new UserReview();
                ur.ReviewID = revID;
                ur.UserID = UserID;
                ur.IsLIke = like;
                ur.IsDislike = disLike;
                ur.ActionID = (short)ActionID;
                ur.CreateDate = DateTime.Now;
                objectContext.UserReviews.AddObject(ur);
                objectContext.SaveChanges();

            }
        }

        #endregion

        #region ---SubScriberUser---

        public void AddSubSciberUser(int UserID, int SubScriberUserID)
        {
            var sub = objectContext.SubScriberUsers.Where(s => s.UserID == UserID && s.SubScriberUserID == SubScriberUserID).FirstOrDefault();
            if (sub == null)
            {
                SubScriberUser suser = new SubScriberUser();
                suser.UserID = UserID;
                suser.SubScriberUserID = SubScriberUserID;
                suser.CreateDate = DateTime.Now;
                objectContext.SubScriberUsers.AddObject(suser);
                objectContext.SaveChanges();
            }
            else
            {
                sub.IsDeleted = null;
                objectContext.SaveChanges();
            }

        }

        public void UnSubScribeUser(int UserID, int SubScriberUserID)
        {
            var sub = objectContext.SubScriberUsers.Where(s => s.UserID == UserID && s.SubScriberUserID == SubScriberUserID).FirstOrDefault();
            if (sub != null)
            {
                sub.IsDeleted = true;
                sub.ModifiredDate = DateTime.Now;
                objectContext.SaveChanges();
            }
        }

        public bool IsUserSubScribed(int userID, int subScriberID)
        {
            var sub = objectContext.SubScriberUsers.Where(s => s.UserID == userID && s.SubScriberUserID == subScriberID && s.IsDeleted == null).FirstOrDefault();
            return sub == null ? false : true;
        }

        #endregion


        #region ---Posters---

        public void AddPosters(string title, string phone, string address, string companyName, string logo, string description, DateTime startdate, DateTime enddate, string timefrom, string timeto)
        {
            //var company = objectContext.Companies.Where(c => c.Name == companyName && c.IsDeleted == null).FirstOrDefault();
            Poster poster = new Poster();
            poster.Title = title;
            poster.CompanyID = int.Parse(companyName); //company != null ? (int?)company.CompanyID : null;
            poster.Phone = phone;
            poster.Address = address;
            poster.Logo = logo;
            poster.CreateDate = DateTime.Now;
            poster.StartDate = startdate;
            poster.EndDate = enddate;
            poster.Description = description;
            poster.TimeFrom = timefrom;
            poster.TimeTo = timeto;
            objectContext.Posters.AddObject(poster);
            objectContext.SaveChanges();
        }

        public void RemovePosterById(int Id)
        {
            var poster = objectContext.Posters.Where(p => p.ID == Id).FirstOrDefault();
            if (poster != null)
            {
                poster.IsDeleted = true;
                objectContext.SaveChanges();
            }
        }
        public void RestorePosterById(int Id)
        {
            var poster = objectContext.Posters.Where(p => p.ID == Id).FirstOrDefault();
            if (poster != null)
            {
                poster.IsDeleted = null;
                objectContext.SaveChanges();
            }
        }

        public List<Poster> GetNewPosters()
        {
            return objectContext.Posters.Include("Company").Where(p => p.IsDeleted == null).OrderByDescending(r => r.CreateDate).ToList();
        }

        public List<Poster> GetPostersByDate(DateTime date)
        {
            var todate = date.Date;
            return objectContext.Posters.Include("Company").Where(r => EntityFunctions.TruncateTime(r.CreateDate) == todate && r.IsDeleted == null).ToList();
        }

        public List<Poster> GetPosterByID(int? Id)
        {
            if (Id == null)
                return objectContext.Posters.ToList();
            else
                return objectContext.Posters.Where(p => p.ID == Id).ToList();
        }

        public Poster GetPosterByID(int Id)
        {
            return objectContext.Posters.Where(p => p.ID == Id).FirstOrDefault();
        }
        #endregion
    }

    #region Extensions

    public static class Extensions
    {

        public static IEnumerable<T> DistinctBy<T, TIdentity>(this IEnumerable<T> source, Func<T, TIdentity> identitySelector)
        {
            return source.Distinct(Extensions.By(identitySelector));
        }
        public static IEqualityComparer<TSource> By<TSource, TIdentity>(Func<TSource, TIdentity> identitySelector)
        {
            return new DelegateComparer<TSource, TIdentity>(identitySelector);
        }

    }
    public class DelegateComparer<T, TIdentity> : IEqualityComparer<T>
    {
        private readonly Func<T, TIdentity> identitySelector;

        public DelegateComparer(Func<T, TIdentity> identitySelector)
        {
            this.identitySelector = identitySelector;
        }

        public bool Equals(T x, T y)
        {
            return Equals(identitySelector(x), identitySelector(y));
        }

        public int GetHashCode(T obj)
        {
            return identitySelector(obj).GetHashCode();
        }
    }
    #endregion

}
